package ext.vpde.cadvaulting.util;

import java.sql.*;
import java.util.*;


public class SJTCSQL {
	
	public static Connection sjtcConn = null;
	
	public static void openConnection() throws Exception {
		Class.forName(SJTCProperties.getDriver());
		String jdbcURL = SJTCProperties.getURL();
		sjtcConn  = DriverManager.getConnection(jdbcURL,SJTCProperties.getDBUser(),SJTCProperties.getDBPass());
	}
	
	public static void closeConnection() throws Exception {
		sjtcConn.close();
	}
	
	public static boolean doSQLSave(String sqlStr) throws Exception {
		boolean returnOK = false;
		openConnection();
		Statement stmt = sjtcConn.createStatement();
		try {
			ResultSet rset = stmt.executeQuery(sqlStr);
			sjtcConn.commit();
			returnOK = true;
		}
		catch (SQLException sqle) { 
			System.out.println(sqle.getMessage() + " " + sqlStr);
			returnOK = false;
		}
		stmt.close();
		closeConnection();
		return returnOK;
	}
	
	public static String doSQLGet(String sqlStr,boolean isString) throws Exception {
		String returnVal = null;
		openConnection();
		Statement stmt = sjtcConn.createStatement();
		try {
			ResultSet rset = stmt.executeQuery(sqlStr);
			rset.next();
			if (isString) {
				returnVal = String.valueOf(rset.getString(1));
			} else {
				returnVal = String.valueOf(rset.getLong(1));
			}
		}
		catch (SQLException sqle) { 
			System.out.println(sqle.getMessage() + " " + sqlStr);
			returnVal = null;
		}
		closeConnection();
		return returnVal;
	}
	
	public static int checkIfReleased(String itemNumber, String version) throws Exception {
		int returnVal = 0;
		openConnection();
		String sqlStr = "select count(*) from sjtcapp.end_en_detail where item_nbr='" + itemNumber + "' and item_type_code='D'";
		String theCount = doSQLGet(sqlStr,false);
		
		if (theCount.equals("1")) {
			returnVal = 1;
			sqlStr += " and revision_level='" + version + "'";
			theCount = doSQLGet(sqlStr,false);
			if (theCount.equals("1")) returnVal = 2;
		}
		
		closeConnection();
		return returnVal;
	}
	
	public static Hashtable getEN(String dateStr) throws Exception {
		Hashtable ht = new Hashtable();
		openConnection();
		Statement stmt = sjtcConn.createStatement();
		String sqlStr  = "select lower(b.item_nbr),b.rev_lvl,b.item_type_code,a.en_nbr,";
		sqlStr += "to_char(a.rel_date,'dd-mon-yyyy hh24:mi:ss'),b.item_name,";
		sqlStr += "to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') ";
		sqlStr += "from sjtcapp.end_en_head a, sjtcapp.end_en_detail b ";
		sqlStr += "where a.status_code='REL' ";
		sqlStr += "and a.en_type in ('normal', 'specification') ";
		sqlStr += "and a.en_nbr=b.en_nbr ";
		sqlStr += "and b.item_type_code in ('C','D','S') ";
		sqlStr += "and b.item_nbr is not null ";
		sqlStr += "and rel_date > to_date('" + dateStr + "','dd-mon-yyyy') order by a.rel_date";
		ResultSet rset = stmt.executeQuery(sqlStr);
		while(rset.next()) {
			
			String itemNumber = String.valueOf(rset.getString(1));
			String revision   = String.valueOf(rset.getString(2));
			String item_type  = String.valueOf(rset.getString(3));
			String enNumber   = String.valueOf(rset.getString(4));
			String relDate    = String.valueOf(rset.getString(5));
			String itemName   = String.valueOf(rset.getString(6));
			String thisDate   = String.valueOf(rset.getString(7));
			String hashKey    = itemNumber + ", " + revision;
			
			String hashVal = itemNumber + "|";
			hashVal += revision + "|";
			hashVal += item_type + "|";
			hashVal += enNumber + "|";
			hashVal += relDate + "|";
			hashVal += itemName + "|";
			hashVal += thisDate;
			ht.put(hashKey,hashVal);

		}
		stmt.close();
		closeConnection();
		return ht;
	}
	
	public static String getDocumentSource(String ida3a4) throws Exception {
		String sqlStr = "select value from stringvalue where ida3a4=" + ida3a4 + " and ida3a6 in ";
		sqlStr += "(select ida2a2 from stringdefinition where name='com.whirlpool.ProductCenterSource')";
		String source = doSQLGet(sqlStr,true);
		return (source == null) ? "wind" : source;
	}
	
	public static String getFileName(String ida2a2) throws Exception {
		String sqlStr = "select filename from applicationdata where ida2a2 in ";
		sqlStr += "(select ida3b5 from holdertocontent where ida3a5=" + ida2a2 + ")";
		return doSQLGet(sqlStr,true);
	}
	
	public static String getFileSize(String ida2a2) throws Exception {
		String sqlStr = "select filesize from applicationdata where ida2a2 in ";
		sqlStr += "(select ida3b5 from holdertocontent where ida3a5=" + ida2a2 + ")";
		return doSQLGet(sqlStr,false);
	}
	
	public static boolean sendPDF(Hashtable ht) throws Exception {
		String PDF_ID  = ht.get("PDF_ID").toString();
		String FILE_ID = ht.get("FILE_ID").toString();
		String REVISION_LEVEL = ht.get("REVISION_LEVEL").toString();
		String PDF_FILE_TITLE = ht.get("PDF_FILE_TITLE").toString();
		String SOURCE   = ht.get("SOURCE").toString();
		String FILENAME = ht.get("FILENAME").toString();
		String FILESIZE = ht.get("FILESIZE").toString();
		System.out.println("FILENAME: " + FILENAME);
		String fileRoot = FILENAME.substring(0,FILENAME.indexOf("."));
		String lastTwo  = fileRoot.substring(fileRoot.length()-2);
		
		String glvLoc   = "/usr3/glvstore/" + SOURCE + "/" + lastTwo + "/" + fileRoot + "/" + REVISION_LEVEL + "/fin/" + FILENAME;
		String glvLink  = "http://ldesrv4.sjtc.whirlpool.com/glv/GlvGetFileServlet?file=" + glvLoc;
		String sqlStr = "insert into glv_pdf (PDF_ID,FILE_ID,REVISION_LEVEL,PDF_FILE_TITLE,SOURCE,PDF_FILE_ID,PDF_FILE_SIZE) values (";
		sqlStr += PDF_ID + ",'" + FILE_ID  + "','" + REVISION_LEVEL + "','" + PDF_FILE_TITLE + "','";
		sqlStr += SOURCE + "','" + FILENAME + "'," + FILESIZE + ")";
		
		doSQLSave(sqlStr);
		sqlStr  = "insert into glv_location (FILE_ID,REVISION_LEVEL,SOURCE,FILE_TYPE,GLV_LOCATION,GLV_LINK,LAST_UPDT_USERID,LAST_UPDT_DATE) ";
		sqlStr += "values ('" + FILE_ID  + "','" + REVISION_LEVEL + "','" + SOURCE + "','fin','" + glvLoc + "','" + glvLink + "','collect',sysdate)";
		System.out.println(sqlStr);
		doSQLSave(sqlStr);
		return true;
	}
}